<?php
define('ROOT_PATH', dirname(__FILE__));
define('IN_SYS', true);
include_once(ROOT_PATH . '/mysqlBase.php');

class mysqlFun{
	public function getDB(){
		if (empty ($this->db)) {
			if (is_file(ROOT_PATH . '/dbConfig.php')) {
				$cfg = include (ROOT_PATH . '/dbConfig.php');
				$dbConf = $cfg['mysql'];
			} else {
				exit ('no db config!');
			}
			$this->db = new MysqlBase();
			$result = $this->db->connect($dbConf['db_host'] . ':' . $dbConf['db_port'], $dbConf['db_username'], $dbConf['db_password'], $dbConf['db_name'],'utf8',true);
		}
		return $this->db;
	}
	
	/**
	 * 更新SQL操作,delete:update:add
	 */
	public function updateQuery($sql) {
		$sqlArray = explode(' ', $sql);
		$sqlType = strtolower($sqlArray[0]);
		$flag = $this->getDB()->query($sql);
		$result = false;
		switch ($sqlArray[0]) {
			case 'replace' :
			case 'insert' :
				$result = $this->getDB()->insert_id() > 0 ? $this->getDB()->insert_id() : $flag;
				break;
			case 'delete' :
				$result = $this->getDB()->affected_rows() > 0 ? $this->getDB()->affected_rows() : $flag;
				break;
			case 'update' :
				$result = $this->getDB()->affected_rows() > 0 ? $this->getDB()->affected_rows() : $flag;
				break;
			default :
				$result = $flag;
				break;
		}
		return $result;
	}

	function getOne($sql) {
		return $this->getDB()->getOne($sql);
	}

	function getRow($sql) {
		return $this->getDB()->getRow($sql);
	}

	function getCol($sql) {
		return $this->getDB()->getCol($sql);
	}

	function getAll($sql) {
		return $this->getDB()->getAll($sql);
	}
	
	public function getError() {
		return $this->getDB()->getError();
	}

	public function startTransaction() {
		return $this->getDB()->query("START TRANSACTION");
	}

	public function rollback() {
		return $this->getDB()->query("ROLLBACK");
	}

	public function commit() {
		return $this->getDB()->query("COMMIT");

	}

	public function update($data, $ids) {
		$fields = array ();
		foreach ($data as $key => $value) {
			$fields[] = "`{$key}`='{$value}'";
		}
		$sql = "update `{$this->table}` set " . implode(',', $fields) . " where `{$this->key}` in(" . implode(',', $ids) . ")";
		return $this->updateQuery($sql);
	}
	
	/**
	 * $data array(key=>value)
	 * $params array(key=>value)
	 */
	public function updateByParams($data,$params){
		if(empty($data) || empty($params)){
			return false;
		}
		$fields = array();
		foreach($data as $key=>$value){
			$fields[] = "`{$key}`='{$value}'";
		}
		$values = array();
		foreach($params as $k=>$v){
			if(is_array($v)){
				$values[] = "`{$k}` in ('".implode("','",$v)."')";
			}else{
				$values[] = "`{$k}`='{$v}'";
			}
		}
		$sql = "update `{$this->table}` set ".implode(',',$fields)." where ".implode(' and ',$values);
		return $this->updateQuery($sql);
	}

	public function add($data) {
		$fieldsKey = array ();
		$fieldsValue = array ();
		foreach ($data as $key => $value) {
			$fieldsKey[] = "`{$key}`";
			/*if($value==''){
				$fieldsValue[] = "null";
			}else{
				$fieldsValue[] = "'{$value}'";
			}*/
			$fieldsValue[] = "'{$value}'";
		}
		$sql = "insert into `{$this->table}`(" . implode(',', $fieldsKey) . ") values(" . implode(',', $fieldsValue) . ")";
		return $this->updateQuery($sql);
	}

	public function delete($ids) {
		$sql = "delete from `{$this->table}` where `{$this->key}` in(" . implode(',', $ids) . ")";
		return $this->updateQuery($sql);
	}
	
	public function find($sql, $pageNum = 0, $numPerPage = 0) {
		$start = intval($pageNum -1) * $numPerPage;
		if ($numPerPage > 0 && $start >= 0) {
			$sql .= " limit {$start},{$numPerPage}";
		}
		return $this->getAll($sql);
	}
	
	public function getTotal($condition = '') {
		$sql = "select count(*) from `{$this->table}` {$condition}";
		return $this->getOne($sql);
	}
	
	public function getObjectByKey($id) {
		return $this->getObject("where `{$this->key}`='{$id}'");
	}
	
	public function getObject($condition=''){
		$sql = "select * from `{$this->table}` {$condition} limit 1";
		return $this->getRow($sql);
	}
	
	public function getMysqlVersion(){
		return $this->getDb()->version();
	}
	
	/*truncate数据表*/
	//获取所有表名
	public function getAllTableName(){
		$sql = "show tables";
		$result = $this->getDB()->query($sql);
		$tableName = array();
		while($row = mysql_fetch_array($result)){
			$tableName[] = $row[0];
		}
		$this->getDB()->free_result($result);
		return $tableName;
	}
	//删除所有表的数据
	public function truncateAllTable(){
		$tableAllName = $this->getAllTableName();
		$errorTableName = "";
		foreach($tableAllName as $tableName){
			$errorTableName .= $this->truncateTableByName($tableName)." ";
		}
		return $errorTableName;
	}
	//根据表名删除表的数据
	public function truncateTableByName($tableName){
		$sqlHead = "truncate table ";
		$sqlBody = $sqlHead.$tableName;
		$result = $this->getDB()->query($sqlBody); 
		if(!$result){
			return $tableName;
		}
	}
	//查找表里面的字段名及备注
	public function showTableFULLFIELDS($tableName){
		$sql = "SHOW FULL FIELDS FROM ".$tableName;
		$result = $this->getDB()->query($sql);
		$tableField = array();
		while($row = mysql_fetch_array($result)){
			$tableField[] = $row['Field']."(".$row['Comment'].")";
		}
		$this->getDB()->free_result($result);
		return $tableField;
	}
	//查找表里面的字段名
	public function showTableField($tableName){
		$sql = "SHOW FULL FIELDS FROM ".$tableName;
		$result = $this->getDB()->query($sql);
		$tableField = array();
		while($row = mysql_fetch_array($result)){
			$tableField[] = $row['Field'];
		}
		$this->getDB()->free_result($result);
		return $tableField;
	}
	
	
}
?>
